import pandas as pd
from openpyxl import load_workbook
import tkinter as tk
from tkinter import filedialog, messagebox
from openpyxl import Workbook

# 创建一个隐藏的Tkinter根窗口
root = tk.Tk()
root.withdraw()

# 弹出文件选择对话框
file_path = filedialog.askopenfilename(title="选择要处理的项目工时统计文件", filetypes=[("Excel files", "*.xlsx")])

if not file_path:
    print("未选择文件，程序退出。")
    exit()

sheet_name = '项目工时归档'
columns_to_extract = [0, 1, 4, 5]  # 第1, 2, 5, 6列的索引

df = pd.read_excel(file_path, sheet_name=sheet_name, usecols=columns_to_extract)

# 检查数据
for index, row in df.iterrows():
    if (row[3] in ["SW设计", "HW设计"]) and (row[1] in ["BA LF阀门生产", "BA LF售后"]):
        messagebox.showerror("错误", f"{row[3]}的{row[0]}填了{row[1]}")
        exit()

# 对数据进行分组和聚合
grouped_df = df.groupby(['项目编号', '所属部门'])['本次用工工时'].sum().reset_index()

# 使用pivot方法将数据转换为行列索引的形式
pivot_df = grouped_df.pivot(index='项目编号', columns='所属部门', values='本次用工工时')

# 移除行标签名
pivot_df.index.name = None

# 保存转换后的数据到新的Excel文件
output_file_path = '行列转置后的数据.xlsx'
pivot_df.to_excel(output_file_path, merge_cells=False)

# 初始化“HW”列
pivot_df['HW'] = 0

# 将“HW设计”列的值复制到“HW”列
if 'HW设计' in pivot_df.columns:
    pivot_df['HW'] += pivot_df['HW设计'].fillna(0)

if 'BA技术部设计HW' in pivot_df.index:
    ba_tech_design_hw = pivot_df.loc['BA技术部设计HW', 'BA技术本部']
    pivot_df.at['BA技术部设计HW', 'HW'] += ba_tech_design_hw if pd.notna(ba_tech_design_hw) else 0

if '制造部' in pivot_df.columns:
    if 'BA技术部设计HW' in pivot_df.index:
        ba_tech_design_hw = pivot_df.loc['BA技术部设计HW', '制造部']
        if pd.notna(ba_tech_design_hw):
            pivot_df.at['BA技术部设计HW', 'HW'] += ba_tech_design_hw
        else:
            pivot_df.at['BA技术部设计HW', 'HW'] += 0

if '制造部' in pivot_df.columns:
    # 处理行索引不以“BA”开头的数据
    manufacturing_non_ba = pivot_df.loc[~pivot_df.index.str.contains('^BA'), '制造部']
    # 对齐索引并填充空值为0
    manufacturing_non_ba = manufacturing_non_ba.reindex(pivot_df.index, fill_value=0)
    pivot_df['HW'] += manufacturing_non_ba.fillna(0)

# 初始化“SW”列
pivot_df['SW'] = 0

# 将“SW设计”列的值复制到“SW”列
if 'SW设计' in pivot_df.columns:
    pivot_df['SW'] += pivot_df['SW设计'].fillna(0)

if 'BA技术部设计SW' in pivot_df.index:
    ba_tech_design_sw = pivot_df.loc['BA技术部设计SW', 'BA技术本部']
    pivot_df.at['BA技术部设计SW', 'SW'] += ba_tech_design_sw if pd.notna(ba_tech_design_sw) else 0

# 初始化“工程”列
pivot_df['工程'] = 0

# 将指定列的值加到“工程”列
engineering_columns = ["工程部", "工程部上海", "工程部北京", "工程部大连", "工程部天津", "工程部无锡", "工程部深圳", "工程部苏州"]
for col in engineering_columns:
    if col in pivot_df.columns:
        pivot_df['工程'] += pivot_df[col].fillna(0)

if 'BA工程' in pivot_df.index:
    ba_engineering = pivot_df.loc['BA工程', 'BA技术本部']
    pivot_df.at['BA工程', '工程'] += ba_engineering if pd.notna(ba_engineering) else 0

if 'BA技术本部' in pivot_df.columns:
    # 处理行索引不以“BA”开头的数据
    manufacturing_non_ba = pivot_df.loc[~pivot_df.index.str.contains('^BA'), 'BA技术本部']
    # 对齐索引并填充空值为0
    manufacturing_non_ba = manufacturing_non_ba.reindex(pivot_df.index, fill_value=0)
    pivot_df['工程'] += manufacturing_non_ba.fillna(0)

# 初始化“制造”列
pivot_df['制造'] = 0

manufacturing_columns = ["制造科", "盘检查", "盘制造", "仓库管理"]
for col in manufacturing_columns:
    if col in pivot_df.columns:
        pivot_df['制造'] += pivot_df[col].fillna(0)

if 'BA制造' in pivot_df.index:
    ba_manufacturing = pivot_df.loc['BA制造', 'BA技术本部']
    pivot_df.at['BA制造', '制造'] += ba_manufacturing if pd.notna(ba_manufacturing) else 0

if '制造部' in pivot_df.columns:
    if 'BA制造' in pivot_df.index:
        manufacturing_ba = pivot_df.loc['BA制造', '制造部']
        if pd.notna(manufacturing_ba):
            pivot_df.at['BA制造', '制造'] += manufacturing_ba
        else:
            pivot_df.at['BA制造', '制造'] += 0


# 初始化“LF”列
pivot_df['LF'] = 0

# 将“LF阀门生产”列的值加到“LF”列
if 'LF阀门生产' in pivot_df.columns:
    pivot_df['LF'] += pivot_df['LF阀门生产'].fillna(0)

if 'BA LF阀门生产' in pivot_df.index:
    ba_lf_valve_production = pivot_df.loc['BA LF阀门生产', 'BA技术本部']
    pivot_df.at['BA LF阀门生产', 'LF'] += ba_lf_valve_production if pd.notna(ba_lf_valve_production) else 0

if '制造部' in pivot_df.columns:
    if 'BA LF阀门生产' in pivot_df.index:
        ba_lf_valve_production = pivot_df.loc['BA LF阀门生产', '制造部']
        if pd.notna(ba_lf_valve_production):
            pivot_df.at['BA LF阀门生产', 'LF'] += ba_lf_valve_production
        else:
            pivot_df.at['BA LF阀门生产', 'LF'] += 0


# 将新加的5列数据中的0替换为空值
pivot_df['HW'] = pivot_df['HW'].replace(0, pd.NA)
pivot_df['SW'] = pivot_df['SW'].replace(0, pd.NA)
pivot_df['工程'] = pivot_df['工程'].replace(0, pd.NA)
pivot_df['制造'] = pivot_df['制造'].replace(0, pd.NA)
pivot_df['LF'] = pivot_df['LF'].replace(0, pd.NA)

# 保存转换后的数据到新的Excel文件
output_file_path = '分部门相加后的数据.xlsx'
pivot_df.to_excel(output_file_path, merge_cells=False, na_rep='')

# 加载生成的Excel文件
wb = load_workbook(output_file_path)
ws = wb.active

# 调整第一行的单元格宽度
for cell in ws[1]:
    ws.column_dimensions[cell.column_letter].width = max(ws.column_dimensions[cell.column_letter].width,
                                                         len(str(cell.value)) + 2)

# 调整第一列的单元格宽度
for row in ws.iter_rows(min_row=2, max_col=1):
    for cell in row:
        ws.column_dimensions[cell.column_letter].width = max(ws.column_dimensions[cell.column_letter].width,
                                                             len(str(cell.value)) + 2)

# 保存调整后的Excel文件
wb.save(output_file_path)

# 删除除了新增加的5列之外的所有列
columns_to_keep = ['HW', 'SW', '工程', '制造', 'LF']
pivot_df = pivot_df[columns_to_keep]

# 保存转换后的数据到新的Excel文件
output_file_path = '分摊后的数据.xlsx'
pivot_df.to_excel(output_file_path, merge_cells=False, na_rep='')

# 加载生成的Excel文件
wb = load_workbook(output_file_path)
ws = wb.active

# 调整第一行的单元格宽度
for cell in ws[1]:
    ws.column_dimensions[cell.column_letter].width = max(ws.column_dimensions[cell.column_letter].width,
                                                         len(str(cell.value)) + 2)

# 调整第一列的单元格宽度
for row in ws.iter_rows(min_row=2, max_col=1):
    for cell in row:
        ws.column_dimensions[cell.column_letter].width = max(ws.column_dimensions[cell.column_letter].width,
                                                             len(str(cell.value)) + 2)

# 保存调整后的Excel文件
wb.save(output_file_path)



# 读取Excel文件
df = pd.read_excel('分摊后的数据.xlsx', sheet_name='Sheet1')

# 筛选出只包含“BA”但不包含“LF”和“PD”的合同号
ba_contracts = df[df.iloc[:, 0].str.contains('BA') & ~df.iloc[:, 0].str.contains('LF|PD')]

# 计算每个部门的汇总金额
ba_sum = ba_contracts.iloc[:, 1:].sum()

# 找出不包含“BA”的普通合同号
non_ba_contracts = df[~df.iloc[:, 0].str.contains('BA')]

# 筛选出金额不为0的普通合同号
non_ba_non_zero = non_ba_contracts[(non_ba_contracts.iloc[:, 1:] != 0).any(axis=1)]

# 计算这些合同号的总金额
non_ba_sum = non_ba_non_zero.iloc[:, 1:].sum()

# 计算比例
ratio = ba_sum / non_ba_sum

# 生成新的合同号
new_contracts = []
for index, row in non_ba_non_zero.iterrows():
    contract_prefix = row.iloc[0][:6]
    new_contract_number = f"{contract_prefix}2024001"
    new_row = [new_contract_number] + (row.iloc[1:] * ratio).tolist()
    new_contracts.append(new_row)

# 创建新的DataFrame
new_contracts_df = pd.DataFrame(new_contracts, columns=df.columns)

# 合并重复的合同号，对应的列的值相加
new_contracts_df = new_contracts_df.groupby(df.columns[0]).sum().reset_index()

# 处理包含 "LF" 或 "PD" 的新合同号
lf_pd_contracts = new_contracts_df[new_contracts_df.iloc[:, 0].str.contains('LF|PD')]
other_contracts = new_contracts_df[~new_contracts_df.iloc[:, 0].str.contains('LF|PD')]

# 遍历包含 "LF" 或 "PD" 的合同号
for index, lf_pd_row in lf_pd_contracts.iterrows():
    contract_number = lf_pd_row.iloc[0]
    for col in new_contracts_df.columns[1:]:
        if lf_pd_row[col] != 0:
            # 计算需要均摊的金额
            amount_to_distribute = lf_pd_row[col]
            # 筛选出不为 0 的其他合同号
            other_non_zero_contracts = other_contracts[other_contracts[col] != 0]
            if not other_non_zero_contracts.empty:
                total_other_amount = other_non_zero_contracts[col].sum()
                for other_index, other_row in other_non_zero_contracts.iterrows():
                    other_contract_number = other_row.iloc[0]
                    other_amount = other_row[col]
                    # 计算均摊后的金额
                    distributed_amount = (other_amount / total_other_amount) * amount_to_distribute
                    # 更新其他合同号的金额
                    other_contracts.at[other_index, col] += round(distributed_amount, 2)

# 删除包含 "LF" 或 "PD" 的合同号的行
final_new_contracts_df = other_contracts

# 格式化金额：将金额为0的值替换为空，其余保留两位小数点
final_new_contracts_df.iloc[:, 1:] = final_new_contracts_df.iloc[:, 1:].applymap(lambda x: '' if x == 0 else round(x, 2))

# 将新生成的合同号的行放到原来数据的最上方
final_df = pd.concat([final_new_contracts_df, df[~df.iloc[:, 0].isin(ba_contracts.iloc[:, 0])]], ignore_index=True)

# 保存结果到原来的Excel文件
final_df.to_excel('分摊后的数据.xlsx', index=False)



import pandas as pd

# 定义部门和地区映射关系
region_mapping = {
    'BA品质管理': 'SH',
    'HW设计': 'SH',
    'LF阀门生产': 'SH',
    'SW设计': 'SH',
    '仓库管理': 'SH',
    '制造科': 'SH',
    '工程部上海': 'SH',
    '工程部无锡': 'SH',
    '工程部苏州': 'SH',
    '盘制造': 'SH',
    '盘检查': 'SH',
    '工程部北京': 'BJ',
    '工程部大连': 'BJ',
    '工程部天津': 'BJ',
    '工程部深圳': 'GZ'
}

# 读取行列转置后的数据
transposed_df = pd.read_excel('行列转置后的数据.xlsx', sheet_name='Sheet1')

# 读取分摊后的数据
df = pd.read_excel('分摊后的数据.xlsx', sheet_name='Sheet1')

# 获取第一列（合同号）
project_number_col = df.columns[0]

# 筛选出合同号包含“BA”和“LF”或者包含“BA”和“PD”的数据
filtered_df_ba_lf = transposed_df[transposed_df[project_number_col].str.contains('BA') & transposed_df[project_number_col].str.contains('LF')]
filtered_df_ba_pd = transposed_df[transposed_df[project_number_col].str.contains('BA') & transposed_df[project_number_col].str.contains('PD')]

# 合并两个筛选结果
filtered_df_ba_lf_pd = pd.concat([filtered_df_ba_lf, filtered_df_ba_pd], ignore_index=True)

# 按部门列进行分组并求和
grouped_df = filtered_df_ba_lf_pd.groupby(df.columns[1:]).sum().reset_index()

# 生成新的合同号和金额
new_contracts = []
for index, row in grouped_df.iterrows():
    department = row.iloc[0]
    region = region_mapping.get(department, '')
    if 'LF' in row[project_number_col] or 'PD' in row[project_number_col]:
        new_contract_number = f"{region}-LF-2024001"
        new_row = [new_contract_number] + row.iloc[1:].tolist()
        new_contracts.append(new_row)

# 创建新的DataFrame
new_contracts_df = pd.DataFrame(new_contracts, columns=df.columns)

# 将新生成的合同号的行放到原来数据的最上方
final_df = pd.concat([new_contracts_df, df], ignore_index=True)

# 保存结果到一个新的DataFrame中
result_df = final_df

# 打印结果DataFrame（可选）
print(result_df)

#
# result_df.to_excel('最终处理后的数据.xlsx', index=False)




#
# # 筛选合同号包含“BA”和“LF”或者包含“BA”和“PD”的数据，并排除特定部门
# filtered_df_lf_pd = pivot_df[
#     pivot_df.index.str.contains('BA') &
#     (pivot_df.index.str.contains('LF') | pivot_df.index.str.contains('PD')) &
#     ~pivot_df.index.isin(excluded_departments)
# ]
#
#
#
# # 将列名转换为地区信息
# filtered_df_lf_pd = filtered_df_lf_pd.stack().reset_index()
# filtered_df_lf_pd.columns = ['项目编号', '所属部门', 'LF类金额']
#
# # 添加地区信息到每一行数据
# filtered_df_lf_pd['地区'] = filtered_df_lf_pd['所属部门'].map(region_mapping)
#
# # 对每个地区的数据进行汇总
# summed_df_lf_pd = filtered_df_lf_pd.groupby('地区')['LF类金额'].sum().reset_index()
#
# # 修改地区的值，在每个地区的值后面加上“LF-2024001”
# summed_df_lf_pd['地区'] = summed_df_lf_pd['地区'] + '-LF-2024001'
#
# # 创建一个新的Excel工作簿
# output_file_path = '分摊后的数据.xlsx'
# wb = Workbook()
# ws = wb.active
#
# # 写入数据，跳过标题行
# for index, row in summed_df_lf_pd.iterrows():
#     # 将“地区”写入第一列
#     ws.cell(row=index + 1, column=1, value=row['地区'])
#     # 将“LF类金额”写入第18列
#     ws.cell(row=index + 1, column=18, value=row['LF类金额'])
#
# # 保存新的Excel文件
# wb.save(output_file_path)

